<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
    
 <%@page import="java.util.*, java.sql.*" %>
 
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Confirmation</title>
</head>
<body>

<% 

	Class.forName("org.postgresql.Driver");
	Connection connect = DriverManager.getConnection("jdbc:postgresql://localhost/tester?user=postgres&password=admin");
	connect.setAutoCommit(false);
	
	try{

	PreparedStatement prepStmt;
	String stmt;
	String studentFieldsList = "(firstname, middleinitial, lastname, citizenship, residence, street, city, zipcode, areacode, telephonecode, telephonenumber, specialization, state)";
	String degreeFieldsList = "(university, discipline, student, date, gpa, type)";
	
	
	
	
    ResultSet rset;
    
	String citizenKey = (String) session.getAttribute("citizenIDSes");
	String residentKey = (String) session.getAttribute("residentIDSes");
	System.err.println("residentKey: " + residentKey);
	
	//String spec = (String) session.getAttribute("degreeSpecializationSes");
	//PreparedStatement specializationsQuery = connect.prepareStatement("SELECT id FROM specializations WHERE specialization = '" + spec + "'");
	//rset = specializationsQuery.executeQuery();
	//String specKey = "";
	//rset.next();
	String specKey = (String) session.getAttribute("specializationIDSes");

	
	//save student table
	stmt = "INSERT INTO students"+studentFieldsList+" VALUES" +
			"('"+session.getAttribute("firstNameSes")+"',"+			//firstName
			"'"+session.getAttribute("middleInitialSes")+"',"+		//MI
			"'"+session.getAttribute("lastNameSes")+"',"+			//lastName
			citizenKey + "," + residentKey + "," +					//citizenship & residence
			"'"+session.getAttribute("streetSes")+"',"+				//street
			"'"+session.getAttribute("citySes")+"',"+				//city
			"'"+session.getAttribute("zipSes")+"',"+				//zipCode
			"'"+session.getAttribute("areaCodeSes")+"',"+			//areaCode
			"'"+session.getAttribute("countryCodeSes")+"',"+		//teleCODE
			"'"+session.getAttribute("phoneSes")+"',"+				//teleNUMBER
			specKey+","+											//spec
			"'"+session.getAttribute("stateSes")+"')";
			
	prepStmt = connect.prepareStatement(stmt);
	prepStmt.executeUpdate();
	
	PreparedStatement appQuery = connect.prepareStatement("SELECT last_value FROM students_id_seq");
	rset = appQuery.executeQuery();
	String appID = "";
	rset.next();
	appID = rset.getString("last_value");
	
	//save degree table
	Vector degreeInfo = (Vector) session.getAttribute("degreeInfoSes");
	for(int i = 0; i < degreeInfo.size(); i=i+8 ){
		
		//university location
		String univLoc = (String) degreeInfo.elementAt(i);
		
		//degreeDiscipline
		String degree = (String) degreeInfo.elementAt(i+1);

	
		stmt = "INSERT INTO degrees"+degreeFieldsList+" VALUES "+
				"("+univLoc+", "+degree+", "+ appID + ", " +
				"'"+degreeInfo.elementAt(i+2)+"',"+
				"'"+degreeInfo.elementAt(i+3)+"',"+
				"'"+degreeInfo.elementAt(i+4)+"')";
		
		
		System.err.println(stmt);
		prepStmt = connect.prepareStatement(stmt);
		prepStmt.executeUpdate();
		
		
		
		
	}
	connect.commit();
	%>
	Thank You!  Your Application has been successfully received.<br><br>

	Your application number is <%= appID %>
	<%
	}
	catch(Exception e)
	{
		%> bad sql. no commit <%
	}	
		
	
	
		
%> 


</body>
</html>